﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class BkcdtDAL:IBkcdtDAL
    {
        public int Add(BkcdtInfo dt)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dt.Dtnum),
                DALHelper.MakeInParam(":num", OracleType.VarChar, dt.Num),
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, dt.Banzu),
                DALHelper.MakeInParam(":tianbao", OracleType.VarChar, dt.Tianbao),
                DALHelper.MakeInParam(":kday", OracleType.VarChar, dt.Kday)
            };
            return (DALHelper.RunCommand(@"insert into dd.BKCDT (dtnum, num, banzu, tianbao, kday) values (:dtnum, :num, :banzu, :tianbao, to_date(:kday,'yyyy-mm-dd hh24:mi:ss'))", prams));
        }
        public int Update(string dtnum, decimal kn, decimal kndc, decimal knfdc, decimal dk, decimal dkdc, decimal dkfdc)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":kn", OracleType.Number, kn),
                DALHelper.MakeInParam(":kndc", OracleType.Number, kndc),
                DALHelper.MakeInParam(":knfdc", OracleType.Number, knfdc),
                DALHelper.MakeInParam(":dk", OracleType.Number, dk),
                DALHelper.MakeInParam(":dkdc", OracleType.Number, dkdc),
                DALHelper.MakeInParam(":dkfdc", OracleType.Number, dkfdc)
            };
            return (DALHelper.RunCommand(@"update dd.BKCDT set kn = :kn, kndc = :kndc, knfdc = :knfdc, dk = :dk, dkdc = :dkdc, dkfdc = :dkfdc where dtnum = :dtnum", prams));
        }
        public int UpdateKN(string dtnum, decimal kn)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":kn", OracleType.Number, kn)
            };
            return (DALHelper.RunCommand(@"update dd.BKCDT set kn = :kn where dtnum = :dtnum", prams));
        }
        public int UpdateKNDC(string dtnum, decimal kndc)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":kndc", OracleType.Number, kndc)
            };
            return (DALHelper.RunCommand(@"update dd.BKCDT set kndc = :kndc where dtnum = :dtnum", prams));
        }
        public int UpdateKNFDC(string dtnum, decimal knfdc)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":knfdc", OracleType.Number, knfdc)
            };
            return (DALHelper.RunCommand(@"update dd.BKCDT set knfdc = :knfdc where dtnum = :dtnum", prams));
        }
        public int UpdateDK(string dtnum, decimal dk)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":dk", OracleType.Number, dk)
            };
            return (DALHelper.RunCommand(@"update dd.BKCDT set dk = :dk where dtnum = :dtnum", prams));
        }
        public int UpdateDKDC(string dtnum, decimal dkdc)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":dkdc", OracleType.Number, dkdc)
            };
            return (DALHelper.RunCommand(@"update dd.BKCDT set dkdc = :dkdc where dtnum = :dtnum", prams));
        }
        public int UpdateDKFDC(string dtnum, decimal dkfdc)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":dkfdc", OracleType.Number, dkfdc)
            };
            return (DALHelper.RunCommand(@"update dd.BKCDT set dkfdc = :dkfdc where dtnum = :dtnum", prams));
        }
        public int Del(string dtnum)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum)
            };
            return (DALHelper.RunCommand(@"delete from dd.BKCDT where DTNUM = :dtnum", prams));
        }
        public DataSet Select(string dtnum)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.BKCDT where dtnum = :dtnum", prams, "jl"));
        }
        public DataSet SelectAll(string ku)
        {
            string nowtime = DateTime.Now.ToShortTimeString().ToString();
            string time1 = "08:00:00";
            string time2 = "20:00:00";
            DateTime dt1 = Convert.ToDateTime(nowtime);
            DateTime dt2 = Convert.ToDateTime(time1);
            DateTime dt3 = Convert.ToDateTime(time2);
            string bt = "";
            string et = "";
            if (DateTime.Compare(dt1, dt2) >= 0 && DateTime.Compare(dt1, dt3) <= 0)
            {
                bt = dt2.ToString();
                et = dt3.ToString();
            }
            else if (DateTime.Compare(dt1, dt2) < 0)
            {
                bt = dt3.AddDays(-1).ToString();
                et = dt1.ToString();
            }
            else
            {
                bt = dt3.ToString();
                et = dt1.ToString();
            }
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select dt.dtnum as dtnum, jg.ku as ku, jg.pinzhong as pinzhong, jg.guige as guige, dt.kday as kday, dt.banzu as banzu, dt.tianbao as tianbao, dt.kn as kn, dt.kndc as kndc, dt.knfdc as knfdc, dt.dk as dk, dt.dkdc as dkdc, dt.dkfdc as dkfdc from dd.BKCDT dt, dd.KC jg where dt.num = jg.num and jg.ku = :ku and dt.kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and dt.kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams, "jlall"));

        }
        public DataSet SelectPG(string ku)
        {
            string nowtime = DateTime.Now.ToShortTimeString().ToString();
            string time1 = "08:00:00";
            string time2 = "20:00:00";
            DateTime dt1 = Convert.ToDateTime(nowtime);
            DateTime dt2 = Convert.ToDateTime(time1);
            DateTime dt3 = Convert.ToDateTime(time2);
            string bt = "";
            string et = "";
            if (DateTime.Compare(dt1, dt2) >= 0 && DateTime.Compare(dt1, dt3) <= 0)
            {
                bt = dt2.ToString();
                et = dt3.ToString();
            }
            else if (DateTime.Compare(dt1, dt2) < 0)
            {
                bt = dt3.AddDays(-1).ToString();
                et = dt1.ToString();
            }
            else
            {
                bt = dt3.ToString();
                et = dt1.ToString();
            }
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select num, pinzhong, guige from dd.KC where ku = :ku and num not in (select num from dd.BKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss'))", prams, "pg"));
        }
        public string KuKC(string cday, string ku)
        {
            
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dt.kn) from dd.BKCDT dt, dd.KC jg where dt.num = jg.num and jg.ku = :ku and dt.kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and dt.kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();

        }
        public string JDBC(string cday)
        {
            
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dk) from dd.BKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();

        }
        public string BCkc(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dk + kn) from dd.BKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string ZKC(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dk + kn) from dd.BKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        public string DBJD(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dt.dk) from dd.BKCDT dt, dd.KC jg where dt.num = jg.num and jg.ku = '大棒线' dt.kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and dt.kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
    }
}
